character_expression

Is an expression of the type char or varchar.

int

The following example assumes an ASCII character set and returns the ASCII value and CHAR character for each character in the string Du monde entier.

SET TEXTSIZE 0;
SET NOCOUNT ON;
-- Create the variables for the current character string position 
-- and for the character string.
DECLARE @position int, @string char(15);
-- Initialize the variables.
SET @position = 1;
SET @string = 'Du monde entier';
WHILE @position <= DATALENGTH(@string)
   BEGIN
   SELECT ASCII(SUBSTRING(@string, @position, 1)),
      CHAR(ASCII(SUBSTRING(@string, @position, 1)))
    SET @position = @position + 1
   END;
SET NOCOUNT OFF;
GO

Here is the result set.

----------- - 
68          D 
              
----------- - 
117         u 
              
----------- - 
32            
              
----------- - 
109         m 
              
----------- - 
111         o 
              
----------- - 
110         n 
              
----------- - 
100         d 
              
----------- - 
101         e 
              
----------- - 
32            
              
----------- - 
101         e 
              
----------- - 
110         n 
              
----------- - 
116         t 
              
----------- - 
105         i 
              
----------- - 
101         e 
              
----------- - 
114         r

integer_expression

Is an integer from 0 through 255. NULL is returned if the integer expression is not in this range.

char(1)

CHAR can be used to insert control characters into character strings. The following table shows some frequently used control characters.

expressionToFind

Is a character expression that contains the sequence to be found. expressionToFind is limited to 8000 characters.

expressionToSearch

Is a character expression to be searched.

start_location

Is an integer or bigint expression at which the search starts. If start_location is not specified, is a negative number, or is 0, the search starts at the beginning of expressionToSearch.

bigint if expressionToSearch is of the varchar(max), nvarchar(max), or varbinary(max) data types; otherwise, int.

If either expressionToFind or expressionToSearch is of a Unicode data type (nvarchar or nchar) and the other is not, the other is converted to a Unicode data type. CHARINDEX cannot be used with text, ntext, and image data types.

If either expressionToFind or expressionToSearch is NULL, CHARINDEX returns NULL.

If expressionToFind is not found within expressionToSearch, CHARINDEX returns 0.

CHARINDEX performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

The starting position returned is 1-based, not 0-based.

0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in CHARINDEX.

string_value

A string value to concatenate to the other values.

String, the length and type of which depend on the input.

CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned. The implicit conversion to strings follows the existing rules for data type conversions. For more information about data type conversions, see CAST and CONVERT (Transact-SQL).

The return type depends on the type of the arguments. The following table illustrates the mapping.

When the arguments are <= 4000 for nvarchar, or <= 8000 for varchar, implicit conversions can affect the length of the result. Other data types have different lengths when they are implicitly converted to strings. For example, an int (14) has a string length of 12, while a float has a length of 32. Thus the result of concatenating two integers has a length of no less than 24.

If none of the input arguments is of a supported large object (LOB) type, then the return type is truncated to 8000 in length, regardless of the return type. This truncation preserves space and supports efficiency in plan generation.

The CONCAT function can be executed remotely on a linked server which is version SQL Server 2012 and above. For older linked servers, the CONCAT operation will be performed locally after the non-concatenated values are returned from the linked server.

character_expression

Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.

int

The integer returned is the number of characters in the SOUNDEX values that are the same. The return value ranges from 0 through 4: 0 indicates weak or no similarity, and 4 indicates strong similarity or the same values.

DIFFERENCE and SOUNDEX are collation sensitive.

In the first part of the following example, the SOUNDEX values of two very similar strings are compared. For a Latin1_General collation DIFFERENCE returns a value of 4. In the second part of the following example, the SOUNDEX values for two very different strings are compared, and for a Latin1_General collation DIFFERENCE returns a value of 0.

-- Returns a DIFFERENCE value of 4, the least possible difference.
SELECT SOUNDEX('Green'), SOUNDEX('Greene'), DIFFERENCE('Green','Greene');
GO
-- Returns a DIFFERENCE value of 0, the highest possible difference.
SELECT SOUNDEX('Blotchet-Halls'), SOUNDEX('Greene'), DIFFERENCE('Blotchet-Halls', 'Greene');
GO

Here is the result set.

----- ----- ----------- 
G650  G650  4           

(1 row(s) affected)
                        
----- ----- ----------- 
B432  G650  0           

(1 row(s) affected)

value

Expression of a supported data type to format. For a list of valid types, see the table in the following Remarks section.

format

nvarchar format pattern.

The format argument must contain a valid .NET Framework format string, either as a standard format string (for example, "C" or "D"), or as a pattern of custom characters for dates and numeric values (for example, "MMMM DD, yyyy (dddd)"). Composite formatting is not supported. For a full explanation of these formatting patterns, please consult the .NET Framework documentation on string formatting in general, custom date and time formats, and custom number formats. A good starting point is the topic, "Formatting Types."

culture

Optional nvarchar argument specifying a culture.

If the culture argument is not provided, the language of the current session is used. This language is set either implicitly, or explicitly by using the SET LANGUAGE statement. culture accepts any culture supported by the .NET Framework as an argument; it is not limited to the languages explicitly supported by SQL Server . If the culture argument is not valid, FORMAT raises an error.

nvarchar or null

The length of the return value is determined by the format.

FORMAT returns NULL for errors other than a culture that is not valid. For example, NULL is returned if the value specified in format is not valid.

FORMAT relies on the presence of .the .NET Framework Common Language Runtime (CLR).

This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

The following table lists the acceptable data types for the value argument together with their .NET Framework mapping equivalent types.

character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression

Is a positive integer that specifies how many characters of the character_expression will be returned. If integer_expression is negative, an error is returned. If integer_expression is type bigint and contains a large value, character_expression must be of a large data type such as varchar(max).

The integer_expression parameter counts a UTF-16 surrogate character as one character.

Returns varchar when character_expression is a non-Unicode character data type.

Returns nvarchar when character_expression is a Unicode character data type.

When using SC collations, the integer_expression parameter counts a UTF-16 surrogate pair as one character. For more information, see Collation and Unicode Support.

string_expression

Is the string expression to be evaluated. string_expression can be a constant, variable, or column of either character or binary data.

bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.

If you are using SC collations, the returned integer value counts UTF-16 surrogate pairs as a single character. For more information, see Collation and Unicode Support.

LEN excludes trailing blanks. If that is a problem, consider using the DATALENGTH (Transact-SQL) function which does not trim the string. If processing a unicode string, DATALENGTH will return twice the number of characters. The following example demonstrates LEN and DATALENGTH with a trailing space.

DECLARE @v1 varchar(40),
    @v2 nvarchar(40);
SELECT 
@v1 = 'Test of 22 characters ', 
@v2 = 'Test of 22 characters ';
SELECT LEN(@v1) AS [varchar LEN] , DATALENGTH(@v1) AS [varchar DATALENGTH];
SELECT LEN(@v2) AS [nvarchar LEN], DATALENGTH(@v2) AS [nvarchar DATALENGTH];

The following example selects the number of characters and the data in FirstName for people located in Australia. This example uses the AdventureWorks2012 database.

SELECT LEN(FirstName) AS Length, FirstName, LastName 
FROM Sales.vIndividualCustomer
WHERE CountryRegionName = 'Australia';
GO

character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

varchar or nvarchar

The following example uses the LOWER function, the UPPER function, and nests the UPPER function inside the LOWER function in selecting product names that have prices between $11 and $20. This example uses the AdventureWorks2012 database.

SELECT LOWER(SUBSTRING(Name, 1, 20)) AS Lower, 
   UPPER(SUBSTRING(Name, 1, 20)) AS Upper, 
   LOWER(UPPER(SUBSTRING(Name, 1, 20))) As LowerUpper
FROM Production.Product
WHERE ListPrice between 11.00 and 20.00;
GO

Here is the result set.

Lower Upper LowerUpper

--------------------- --------------------- --------------------

minipump MINIPUMP minipump

taillights - battery TAILLIGHTS - BATTERY taillights - battery

(2 row(s) affected)

character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression must be of a data type, except text, ntext, and image, that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

varchar or nvarchar

The following example uses LTRIM to remove leading spaces from a character variable.

DECLARE @string_to_trim varchar(60);
SET @string_to_trim = '     Five spaces are at the beginning of this
   string.';
SELECT 'Here is the string without the leading spaces: ' + 
   LTRIM(@string_to_trim);
GO

Here is the result set.

------------------------------------------------------------------------
Here is the string without the leading spaces: Five spaces are at the beginning of this string.             

(1 row(s) affected)

integer_expression

When the collation of the database does not contain the supplementary character (SC) flag, this is a positive whole number from 0 through 65535 (0 through 0xFFFF). If a value outside this range is specified, NULL is returned. For more information about supplementary characters, see Collation and Unicode Support.

When the collation of the database supports the supplementary character (SC) flag, this is a positive whole number from 0 through 1114111 (0 through 0x10FFFF). If a value outside this range is specified, NULL is returned.

nchar(1) when the default database collation does not support supplementary characters.

nvarchar(2) when the default database collation supports supplementary characters.

If the parameter integer_expression lies in the range 0 - 0xFFFF, only one character is returned. For higher values, NCHAR returns the corresponding surrogate pair. Do not construct a surrogate pair by using NCHAR(<High surrogate>) + NCHAR(<Low Surrogate>). Instead, use a database collation that supports supplementary characters and then specify the Unicode codepoint for the surrogate pair. The following example demonstrates both the old style method of constructing a surrogate pair and the preferred method of specifying the Unicode codepoint.

CREATE DATABASE test COLLATE Finnish_Swedish_100_CS_AS_SC;
DECLARE @d nvarchar(10) = N'𣅿'; 
-– Old style method.
SELECT NCHAR(0xD84C) + NCHAR(0xDD7F); 

-- Preferred method. 
SELECT NCHAR(143743); 

-- Alternative preferred method.
SELECT NCHAR(UNICODE(@d));  

pattern

Is a character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern (except when you search for first or last characters). pattern is an expression of the character string data type category. pattern is limited to 8000 characters. 

expression

Is an expression, typically a column that is searched for the specified pattern. expression is of the character string data type category.

bigint if expression is of the varchar(max) or nvarchar(max) data types; otherwise int.

If either pattern or expression is NULL, PATINDEX returns NULL.

PATINDEX performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

'character_string'

Is a string of Unicode character data. character_string is sysname and is limited to 128 characters. Inputs greater than 128 characters return NULL.

'quote_character'

Is a one-character string to use as the delimiter. Can be a single quotation mark ( ' ), a left or right bracket ( [ ] ), or a double quotation mark ( " ). If quote_character is not specified, brackets are used.

nvarchar(258)

The following example takes the character string abc[]def and uses the [ and ] characters to create a valid SQL Server delimited identifier.

SELECT QUOTENAME('abc[]def');

Here is the result set.

[abc[]]def]

(1 row(s) affected)

Notice that the right bracket in the string abc[]def is doubled to indicate an escape character.

string_expression

Is the string expression to be searched. string_expression can be of a character or binary data type.

string_pattern

Is the substring to be found. string_pattern can be of a character or binary data type. string_pattern cannot be an empty string (''), and must not exceed the maximum number of bytes that fits on a page.

string_replacement

Is the replacement string. string_replacement can be of a character or binary data type.

Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.

Returns NULL if any one of the arguments is NULL.

If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.

REPLACE performs comparisons based on the collation of the input. To perform a comparison in a specified collation, you can use COLLATE to apply an explicit collation to the input.

0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in REPLACE.

The following example replaces the string cde in abcdefghi with xxx.

SELECT REPLACE('abcdefghicde','cde','xxx');
GO

Here is the result set.

------------
abxxxfghixxx
(1 row(s) affected)

The following example uses the COLLATE function.

SELECT REPLACE('This is a Test'  COLLATE Latin1_General_BIN,
'Test', 'desk' );
GO

Here is the result set.

------------
This is a desk
(1 row(s) affected)

string_expression

Is an expression of a character string or binary data type. string_expression can be either character or binary data.

Note
If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

integer_expression

Is an expression of any integer type, including bigint. If integer_expression is negative, NULL is returned.

Returns the same type as string_expression.

string_expression

string_expression is an expression of a string or binary data type. string_expression can be a constant, variable, or column of either character or binary data.

varchar or nvarchar

string_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert string_expression.

The following example returns all contact first names with the characters reversed. This example uses the AdventureWorks2012 database.

SELECT FirstName, REVERSE(FirstName) AS Reverse
FROM Person.Person
WHERE BusinessEntityID < 5
ORDER BY FirstName;
GO

Here is the result set.

FirstName Reverse

-------------- --------------

Ken neK

Rob boR

Roberto otreboR

Terri irreT

(4 row(s) affected)

The following example reverses the characters in a variable.

DECLARE @myvar varchar(10);
SET @myvar = 'sdrawkcaB';
SELECT REVERSE(@myvar) AS Reversed ;
GO

The following example makes an implicit conversion from an int data type into varchar data type and then reverses the result.

SELECT REVERSE(1234) AS Reversed ;
GO

character_expression

Is an expression of character or binary data. character_expression can be a constant, variable, or column. character_expression can be of any data type, except text or ntext, that can be implicitly converted to varchar or nvarchar. Otherwise, use the CAST function to explicitly convert character_expression.

integer_expression

Is a positive integer that specifies how many characters of character_expression will be returned. If integer_expression is negative, an error is returned. If integer_expression is type bigint and contains a large value, character_expression must be of a large data type such as varchar(max).

Returns varchar when character_expression is a non-Unicode character data type.

Returns nvarchar when character_expression is a Unicode character data type.

When using SC collations, the RIGHT function counts a UTF-16 surrogate pair as a single character. For more information, see Collation and Unicode Support.

The following example returns the five rightmost characters of the first name for each person in the AdventureWorks2012 database.

SELECT RIGHT(FirstName, 5) AS 'First Name'
FROM Person.Person
WHERE BusinessEntityID < 5
ORDER BY FirstName;
GO

Here is the result set.

First Name
----------
Ken
Terri
berto
Rob

(4 row(s) affected)

character_expression

Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

character_expression must be of a data type that is implicitly convertible to varchar. Otherwise, use CAST to explicitly convert character_expression.

varchar or nvarchar

character_expression

Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.

varchar

SOUNDEX converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken. The first character of the code is the first character of character_expression, converted to upper case. The second through fourth characters of the code are numbers that represent the letters in the expression. The letters A, E, I, O, U, H, W, and Y are ignored unless they are the first letter of the string. Zeroes are added at the end if necessary to produce a four-character code. For more information about the SOUNDEX code, see The Soundex Indexing System.

SOUNDEX codes from different strings can be compared to see how similar the strings sound when spoken. The DIFFERENCE function performs a SOUNDEX on two strings, and returns an integer that represents how similar the SOUNDEX codes are for those strings.

SOUNDEX is collation sensitive. String functions can be nested.

In previous versions of SQL Server, the SOUNDEX function applied a subset of the SOUNDEX rules. Under database compatibility level 110 or higher, SQL Server applies a more complete set of the rules.

After upgrading to compatibility level 110 or higher, you may need to rebuild the indexes, heaps, or CHECK constraints that use the SOUNDEX function.

• A heap that contains a persisted computed column defined with SOUNDEX cannot be queried until the heap is rebuilt by running the statement ALTER TABLE <table> REBUILD.

• CHECK constraints defined with SOUNDEX are disabled upon upgrade. To enable the constraint, run the statement ALTER TABLE <table> WITH CHECK CHECK CONSTRAINT ALL.

• Indexes (including indexed views) that contain a persisted computed column defined with SOUNDEX cannot be queried until the index is rebuilt by running the statement ALTER INDEX ALL ON <object> REBUILD.

The following example shows the SOUNDEX function and the related DIFFERENCE function. In the first example, the standard SOUNDEX values are returned for all consonants. Returning the SOUNDEX for Smith and Smythe returns the same SOUNDEX result because all vowels, the letter y, doubled letters, and the letter h, are not included.

-- Using SOUNDEX
SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe');

Here is the result set. Valid for a Latin1_General collation.

----- ----- 
S530  S530  

(1 row(s) affected)

The DIFFERENCE function compares the difference of the SOUNDEX pattern results. The following example shows two strings that differ only in vowels. The difference returned is 4, the lowest possible difference.

-- Using DIFFERENCE
SELECT DIFFERENCE('Smithers', 'Smythers');
GO

Here is the result set. Valid for a Latin1_General collation.

----------- 
4           

(1 row(s) affected)

In the following example, the strings differ in consonants; therefore, the difference returned is 2, the greater difference.

SELECT DIFFERENCE('Anothers', 'Brothers');
GO

Here is the result set. Valid for a Latin1_General collation.

----------- 
2           

(1 row(s) affected)

integer_expression

Is a positive integer that indicates the number of spaces. If integer_expression is negative, a null string is returned.

For more information, see Expressions (Transact-SQL)

varchar

To include spaces in Unicode data, or to return more than 8000 character spaces, use REPLICATE instead of SPACE.

The following example trims the last names and concatenates a comma, two spaces, and the first names of people listed in the Person table in AdventureWorks2012.

USE AdventureWorks2012;
GO
SELECT RTRIM(LastName) + ',' + SPACE(2) +  LTRIM(FirstName)
FROM Person.Person
ORDER BY LastName, FirstName;
GO

float_expression

Is an expression of approximate numeric (float) data type with a decimal point.

length

Is the total length. This includes decimal point, sign, digits, and spaces. The default is 10.

decimal

Is the number of places to the right of the decimal point. decimal must be less than or equal to 16. If decimal is more than 16 then the result is truncated to sixteen places to the right of the decimal point.

varchar

If supplied, the values for length and decimal parameters to STR should be positive. The number is rounded to an integer by default or if the decimal parameter is 0. The specified length should be greater than or equal to the part of the number before the decimal point plus the number's sign (if any). A short float_expression is right-justified in the specified length, and a long float_expression is truncated to the specified number of decimal places. For example, STR(12,10) yields the result of 12. This is right-justified in the result set. However, STR(1223,2) truncates the result set to **. String functions can be nested.

Note
To convert to Unicode data, use STR inside a CONVERT or CAST conversion function.

The following example converts an expression that is made up of five digits and a decimal point to a six-position character string. The fractional part of the number is rounded to one decimal place.

SELECT STR(123.45, 6, 1);
GO

Here is the result set.

------
 123.5

(1 row(s) affected)

When the expression exceeds the specified length, the string returns ** for the specified length.

SELECT STR(123.45, 2, 2);
GO

Here is the result set.

--
**

(1 row(s) affected)

Even when numeric data is nested within STR, the result is character data with the specified format.

SELECT STR (FLOOR (123.45), 8, 3;)
GO

Here is the result set.

--------
 123.000

(1 row(s) affected)

character_expression

Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

start

Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.

length

Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. length can be of type bigint.

replaceWith_expression

Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data. This expression will replace length characters of character_expression beginning at start.

Returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.

If the start position or the length is negative, or if the starting position is larger than length of the first string, a null string is returned. If the start position is 0, a null value is returned. If the length to delete is longer than the first string, it is deleted to the first character in the first string.

An error is raised if the resulting value is larger than the maximum supported by the return type.

The following example returns a character string created by deleting three characters from the first string, abcdef, starting at position 2, at b, and inserting the second string at the deletion point.

SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO

Here is the result set.

--------- 
aijklmnef 

(1 row(s) affected)

expression

Is a character, binary, text, ntext, or image expression.

start

Is an integer or bigint expression that specifies where the returned characters start. If start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length- 1 or 0. If start is greater than the number of characters in the value expression, a zero-length expression is returned.

length

Is a positive integer or bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

Returns character data if expression is one of the supported character data types. Returns binary data if expression is one of the supported binary data types. The returned string is the same type as the specified expression with the exceptions shown in the table.

The values for start and length must be specified in number of characters for ntext, char, or varchar data types and bytes for text, image, binary, or varbinary data types.

The expression must be varchar(max) or varbinary(max) when the start or length contains a value larger than 2147483647.